#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/10/21 11:53
# @Author : 郭晗
# @File    : sku_filter.py
import datetime

import requests

from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
import os
import threading
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class SkuFilter:
    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

    source_id = 14267
    product_rules_template_id = None
    # listing模版 listing_filter
    listing_filter = {
        # 首单库存设置
        "first_time": None,
        # 可用库存
        "good_num": None,
        # 浏览量
        "views": [1,2],
        # listing销量
        "sale": None,
        # 销量（SKU）
        "sku_sales_platform": 'all',
        "sku_sales_days": None,
        "sku_sales": None,
        # 上架次数
        # "publish_count": 1,
        # 产品上架到店铺的时间
        "first_publist_time": None
    }
    # 活动模版  activity_template  price 和 profit 有且只能设置一个
    # activity_template = {
    #     # "price":{
    #     #     "discount":0.7,
    #     #     "range":[0, 1]
    #     #         },
    #     "profit": {
    #         "CalculationFormulaId":1038,
    #        "discount": 0.7,
    #         "Country":'CL',
    #         "range": [0,100]
    #             }
    # }

    # sku_list = {'10001240'}

    def sku_filter(self):


        sql1 = """
        SELECT tsl.id,tsl.first_publish_time
        FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing tsl
        WHERE tsl.source_id=%s 
        AND tsl.`status`=1
        """
        res1 = self.db.fetch_all_tuple(sql1, (self.source_id,))
        if len(res1):
            data = list(map(lambda x: (x[0], x[1]), res1))
            # 筛选出所有的在线的listing
            # 过滤掉未进行折扣活动listing
            data = list(filter(self.filter_listing_by_discount, data))
            # # 执行队列 过滤
            # data = list(filter(self.filter_queue_detail, data))
            # 产品上架到店铺的时间过滤
            if self.listing_filter.get("first_publist_time"):
                data = list(filter(self.filter_listing_by_first_publish_time, data))
            # listing销量 过滤 model
            if self.listing_filter.get("sale"):
                data = list(filter(self.listing_sale_filter, data))

            list_set = set(map(lambda x:x[0],data))
            get_model_id_list = """
            SELECT sku,model_id FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku WHERE listing_id in %s
            """
            # list_set = {2112596}
            model_id_list = self.db.fetch_all_tuple(get_model_id_list, (list_set,))
            if len(model_id_list):
                model_id_list = list(model_id_list)
                # 浏览量 过滤 model
                if self.listing_filter.get("views"):
                    model_id_list = list(filter(self.views_filter, model_id_list))
                # 首单库存时间 过滤 model
                if self.listing_filter.get("first_time"):
                    model_id_list = list(filter(self.first_time_filter, model_id_list))
                # 可用库存 过滤  model
                if self.listing_filter.get("good_num"):
                    model_id_list = list(filter(self.dynamic_inventory_filter, model_id_list))
                # 平台销量 过滤 model
                if self.listing_filter.get("sku_sales"):
                    model_id_list = list(filter(self.sku_sales_filter, model_id_list))
                # if self.activity_template.get("price"):
                #     data = list(filter(self.filter_sku_price, data))
                # if self.activity_template.get("profit"):
                #     data = list(filter(self.filter_sku_priceprofit, data))

                # # 上架次数 过滤
                # if self.listing_filter.get("publish_count") or self.listing_filter.get("publish_count") == 0:
                #     data = list(filter(self.publish_count_filter, data))
                print(model_id_list)
                print(len(model_id_list))



    def publish_count_filter(self, listing_id):
        get_sku_list = """
        SELECT sku FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku a
        WHERE a.listing_id=%s
        AND a.is_deleted=0
        """
        sku_list = self.db.fetch_all_tuple(get_sku_list, (listing_id[0],))
        sku_list = list(map(lambda x: x[0], sku_list))
        if sku_list:
            for sku in sku_list:
                is_true = self.check_publish_count_by_sku(sku)
                if not is_true:
                    ERROR.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了 原因是 不符合可用库存")
                    return False
            return True
        else:
            ERROR.logger.info(
                f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了 原因是没有在t_shopee_listing_sku查询到sku 导致无法查询到可用库存")
            return False

    def filter_listing(self, sku):
        sql2 = """
        SELECT b.id FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku a 
        LEFT JOIN test_zhcxkj_center_listing_comprehensive.t_shopee_listing b ON a.listing_id=b.id
        WHERE a.source_id=%s
        AND a.sku = %s
        AND b.`status` =1;
        """
        res2 = self.db.fetch_all_tuple(sql2, (self.source_id, sku))
        if res2:
            return True

        else:
            INFO.logger.info(f"source_id= {self.source_id} t_shopee_listing_sku表过滤了sku={sku}")
            return False

    def filter_queue_detail(self, sku):
        sql3 = """
        SELECT id FROM test_zhcxkj_center_strategy.t_shopee_publish_execute_queue 
        WHERE source_id=%s AND sku=%s AND `Status` IN (1,2);
        """
        res2 = self.db.fetch_all_tuple(sql3, (self.source_id, sku))
        if res2:
            INFO.logger.info(f"source_id= {self.source_id} t_shopee_publish_execute_queue表过滤了sku={sku}")
            return False

        else:
            return True

    def dynamic_inventory_filter(self, model_id):
        # model_id = ('16366941', 21778310625)
        good_num = self.get_good_num(model_id[0])
        if self.listing_filter.get("good_num")[0] <= good_num <= self.listing_filter.get("good_num")[1]:
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 动态库存过滤了model_id={model_id[1]}")
            return False



    def views_filter(self, model_id):
        # model_id = ('16366941', 21778310625)
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        sql = """
        SELECT SUM(b.views) as views
        FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku a
        LEFT JOIN test_zhcxkj_center_listing_comprehensive.t_shopee_listing_extra b ON a.listing_id=b.listing_id
        WHERE  a.sku=%s
        """
        res = self.db.fetch_all(sql, (model_id[0],))
        if res:
            views = int(res[0].get('views'))
            if self.listing_filter.get('views')[0] <= views <= self.listing_filter.get('views')[1]:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} model_id={model_id[1]} 原因是浏览量")
                return False
        else:
            # INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]} 原因是浏览量")
            views = 0
            if self.listing_filter.get('views')[0] <= views <= self.listing_filter.get('views')[1]:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} model_id={model_id[1]} 原因是浏览量")
                return False


    def listing_sale_filter(self, listing_id):
        # model_id = ('16366941', 21778310625)
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        get_listing_sale = """
        SELECT sale
        FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_extra a
        WHERE  a.listing_id=%s
        """
        res = self.db.fetch_all(get_listing_sale, (listing_id[0],))
        if res:
            sale = int(res[0].get('sale'))
            if self.listing_filter.get('sale')[0] <= sale <= self.listing_filter.get('sale')[1]:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]} 原因是listing销量")
                return False
        else:
            # INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]} 原因是浏览量")
            sale = 0
            if self.listing_filter.get('sale')[0] <= sale <= self.listing_filter.get('sale')[1]:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]} 原因是listing销量")
                return False



    def sku_sales_filter(self, model_id):
        # model_id = ('16366941', 21778310625)
        # 平台销量 过滤 model
        return self.check_sku_sales(model_id)


    def filter_sku_price(self, listing_id):
        #   根据策略规则获取销量
        get_sku_list = """
        SELECT original_price FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku a
        WHERE a.listing_id=%s
        AND a.is_deleted=0
        """
        original_price_list = self.db.fetch_all_tuple(get_sku_list, (listing_id[0],))
        original_price_list = list(map(lambda x: x[0], original_price_list))
        if original_price_list:
            for original_price in original_price_list:
                discount_price = self.activity_template.get("price").get("discount") * float(original_price)
                left_price = float(self.activity_template.get("price").get("range")[0]) * float(original_price)
                right_price = float(self.activity_template.get("price").get("range")[1])* float(original_price)
                if left_price <= discount_price <= right_price:
                    pass
                else:
                    ERROR.logger.info(
                        f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了 原因是不符合价格区间")
                    return False
            return True
        else:
            ERROR.logger.info(
                f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了 原因是没有在t_shopee_listing_sku查询到sku 导致无法查询到SKU销量")
            return False


    def filter_sku_priceprofit(self, listing_id):
        #   根据策略规则获取销量
        get_sku_list = """
        SELECT original_price ,sku
        FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku a
        WHERE a.listing_id=%s
        AND a.is_deleted=0
        """
        original_price_list = self.db.fetch_all_tuple(get_sku_list, (listing_id[0],))
        original_price_list = list(map(lambda x: (x[0],x[1]), original_price_list))
        if original_price_list:
            for original_price in original_price_list:
                discount_price = self.activity_template.get("profit").get("discount") * float(original_price[0])
                GrossProfitMargin = self.get_profit_margin(self.source_id, self.activity_template.get('profit').get("Country"),original_price[1],self.activity_template.get('profit').get("CalculationFormulaId"),discount_price)

                left_profit_margin = float(self.activity_template.get("profit").get("range")[0])
                right_profit_margin = float(self.activity_template.get("profit").get("range")[1])
                if left_profit_margin <= GrossProfitMargin <= right_profit_margin:
                    pass
                else:
                    ERROR.logger.info(
                        f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了 原因是不符合利润率区间")
                    return False
            return True
        else:
            ERROR.logger.info(
                f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了 原因是没有在t_shopee_listing_sku查询到sku 导致无法匹配是否符合利润率区间")
            return False
    def first_time_filter(self, model_id):
        return self.check_Complies_with_first_order_inventory(model_id)


    def filter_listing_by_discount(self, listing_id):
        sql = """
        SELECT dp.sku FROM `test_zhcxkj_center_promotion`.`t_shopee_discount` d 
        INNER JOIN `test_zhcxkj_center_promotion`.`t_shopee_discount_product` dp ON d.id=dp.shopee_discount_id 
        WHERE d.`status`=1 AND d.is_deleted=0 AND dp.is_deleted=0 AND dp.listing_id=%s
        """
        res = self.db.fetch_all(sql, (listing_id[0],))
        if res:
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id}被过滤，原因是未参加折扣活动")
            # print(listing_id)
            return False

    def get_good_num(self, sku):
        is_group_sql = """SELECT is_group FROM test_zhcxkj_center_product.rb_product WHERE sku=%s;"""
        is_group_sql_res = self.db.fetch_all(is_group_sql, (sku,))
        if is_group_sql_res:
            if is_group_sql_res[0]['is_group'] == 0:
                filter_good_num = """
                SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory WHERE sku=%s;
                """
                res1 = self.db.fetch_all(filter_good_num, (sku,))
                if res1[0].get('good_num'):
                    # print(f"{sku}动态库存：{res1[0].get('good_num')}")
                    return res1[0].get('good_num')
                else:
                    # print(f"{sku}动态库存：0")
                    return 0
            else:
                get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                                "WHERE group_sku=%s;"
                get_child_sku_res = self.db.fetch_all(get_child_sku, (sku,))

                if get_child_sku_res:
                    sku_list = []
                    for child in get_child_sku_res:
                        filter_good_num = """
                            SELECT SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory 
                            WHERE sku = %s  group by sku;"""
                        filter_good_num_res = self.db.fetch_all(filter_good_num, (child.get('sku'),))
                        if filter_good_num_res:
                            good_num = filter_good_num_res[0].get('good_num')
                        else:
                            good_num = 0
                        dongtaikucun = int(good_num / (child.get('sku_num')))
                        sku_list.append((child.get('sku'), dongtaikucun))
                    min_sku = sorted(sku_list, key=lambda x: x[1], reverse=False)[0]
                    finally_good_num = min_sku[1]
                    # print(f"{sku}动态库存：{finally_good_num}")
                    return finally_good_num
                else:
                    # print(f"{sku}在rb_product_group_sku表没查询到子sku")
                    return 0

    def get_platform_sales(self, sku, orderSourceType, sku_sales_days):
        """
        得到各个平台销量
        """
        url = """https://api-dw.zhcxkj.com/dw/erp/salesvolume/platform/salessku"""
        if orderSourceType != "all":
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": orderSourceType,
                    "skus": [sku],
                    "days": sku_sales_days
                }
            }
        else:
            param = {
                "input": {
                    "customerId": 1,
                    "orderSourceType": None,
                    "skus": [sku],
                    "days": sku_sales_days
                }
            }
        hearders = {
            "Content-Type": "application/json",
            "Authorization": "Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA==",
        }

        res = requests.post(url=url, headers=hearders, json=param)

        sales = 0
        if isinstance(orderSourceType, int):
            for data in res.json().get('data'):
                if data.get('orderSourceType') == orderSourceType:
                    sales += data.get('salesVolume')
            return sales
        else:
            for data in res.json().get('data'):
                sales += data.get('salesVolume')
            return sales

    def get_profit_margin(self, ordersource_id, country, sku, calculationformulaid, price):
        """
        得到利润率
        :param ordersource_id:
        :param country:
        :param sku:
        :param calculationformulaid:
        :param price:
        :return:
        """
        url = "http://priceapi.zhcxkj.com/api/calculationFormula/calculationPrice/getCalculationGrossProfitMarginViewModelList"
        headers = {
            "Authorization": "Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA==",

        }
        req_json = {
            "language": "zh_CN",
            "input": [
                {
                    "customerId": 1,
                    "BusinessTypeCode": "ShopeeV2LocalWarehouse",
                    "PlatformId": 97,
                    "WarehouseTypeId": 1,
                    "WarehouseId": 5312,
                    "orderSourceId": ordersource_id,
                    "Country": country,
                    "Sku": sku,
                    "CalculationFormulaId": calculationformulaid,
                    "Price": price
                }
            ]
        }
        res = requests.post(url=url, headers=headers, json=req_json)
        if res.status_code == 200:
            return res.json().get('data')[0].get('GrossProfitMargin')
        else:
            return False

    def filter_listing_by_first_publish_time(self, listing_id):
        first_publish_time = listing_id[1]
        first_publist_time_min = datetime.datetime.strptime(self.listing_filter.get("first_publist_time")[0],
                                                            '%Y-%m-%d %H:%M:%S')
        first_publist_time_max = datetime.datetime.strptime(self.listing_filter.get("first_publist_time")[1],
                                                            '%Y-%m-%d %H:%M:%S')
        if first_publish_time:
            if first_publist_time_min <= first_publish_time <= first_publist_time_max:
                return True
            else:
                INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了，原因是不符合上架到店铺时间")
                return False
        else:
            INFO.logger.info(f"source_id= {self.source_id} listing_id={listing_id[0]}被过滤了，没有查询到上架到店铺时间")
            return False

    def check_Complies_with_first_order_inventory(self, model_id):

        headers = {
            "Authorization": "Basic YXBpdXNlcjpFN0kxLWxJX2QjOGRscw=="
        }
        url = "http://erpapi.zhcxkj.com/Product/MarketPublish/GetSKUWarehousing"
        json_data = {
            "current": 1,
            "pageSize": 500,
            "input": {
                "CustomerId": 1,
                "CheckTime": "2022-01-01",
                "Skus": [model_id[0]]
            }
        }
        res2 = requests.post(url=url, json=json_data, headers=headers).json()
        if res2.get("data"):
            puttime = res2.get('data')[0].get('putTime')
            puttime = datetime.datetime.strptime(puttime, '%Y-%m-%d %H:%M:%S.%f')
            first_time_min = datetime.datetime.strptime(self.listing_filter.get("first_time")[0], '%Y-%m-%d %H:%M:%S')
            first_time_max = datetime.datetime.strptime(self.listing_filter.get("first_time")[1], '%Y-%m-%d %H:%M:%S')
            if first_time_min <= puttime <= first_time_max:
                return True
            else:
                INFO.logger.info(f"model_id{model_id[1]}不符合首单库存区间 被过滤")
                return False
        else:
            INFO.logger.info(f"model_id{model_id[1]}没有查询到首单库存时间 被过滤")
            return False

    def check_dynamic_inventory(self, sku):
        good_num = self.get_good_num(sku)
        if self.listing_filter.get("good_num")[0] <= good_num <= self.listing_filter.get("good_num")[1]:
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 动态库存过滤了sku={sku}")
            return False

    def check_sku_sales(self, model_id):
        sales = self.get_platform_sales(model_id[0], self.listing_filter.get("sku_sales_platform"),
                                        self.listing_filter.get("sku_sales_days"))
        # 判断销量是否符合规则
        if self.listing_filter.get("sku_sales")[0] <= sales <= self.listing_filter.get("sku_sales")[1]:
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 平台总销量过滤了sku={model_id[1]}")
            return False

    def check_publish_count_by_sku(self, sku):
        sql = """
        SELECT count(1) as num FROM test_zhcxkj_center_listing_comprehensive.t_shopee_listing_sku WHERE source_id=%s AND sku=%s;
        """
        res = self.db.fetch_all(sql, (self.source_id, sku))
        if res:
            count = res[0].get('num')
        else:
            count = 0
        if count <= self.listing_filter.get("publish_count"):
            return True
        else:
            INFO.logger.info(f"source_id= {self.source_id} 上架次数过滤了sku={sku}")
            return False




if __name__ == '__main__':
    SkuFilter().sku_filter()
    # print(SkuFilter().views_filter([2112596,]))
    # print(SkuFilter().filter_sku_priceprofit((1779045,)))